/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package datapackage;

import java.sql.Statement;
import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;

import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

/**
 *
 * @author salim
 * update on 29/9/2010 ajout du valeur absolu (abs) List<IntituleProf> AvoirMPparProfPJ(..)
 *
 */
public class DivertList {
    // variables globales
    //for database connection
    // String url="jdbc:MySQL://localhost:3306/tables_isae";
    //String user="root";
    //  String password="admin";
    // String url="jdbc:MySQL://10.130.0.12:3306/tables_isae";
    //String user="salim";
    // String password="salim";
    //List<UeProf> lUeProf=new LinkedList<UeProf>();
    //pour avoir un liste des noenseig aussi pour un gradeaff donnee

    // public List<noenseig> noenseiggfl = new LinkedList<noenseig>();
    //variable public accessible pour plusieurs methodes,initializer a chaque fois nous appelons avoirhorairehebo
    // List<horairehebdo> horairehebdototal = new LinkedList<horairehebdo>();
    //Connection connection=null;
    //constructeur
    public DivertList() {
    }


    /* public void addUeProf(UeProf e){
     *    lUeProf.add(e);
     * }
     *public List getUeProf()  {
     *   return lUeProf;
    }*/
    // methode main pour tester les methodes avoirAnnees avoirCentre avoirUeProf
    public static List<annee> Avoirannees() throws SQLException {
        Connection connection = null;
        List<annee> lannee = new LinkedList<annee>();

        try {
            //  connection=getJdbcTables_isae().getConnection();
            connection = getJdbcIsae().getConnection();
        } catch (NamingException ex) {
            Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
        }

        //Connection connection =DriverManager.getConnection(url,user,password);

        PreparedStatement ps = connection.prepareStatement("select annee from ListeAnnees order by annee desc;");
        ResultSet rs = ps.executeQuery();
        //String record = null;

        //int i=0;

        //System.out.println("++++@DivertList-avoirannee executee......");
        while (rs.next()) {

            lannee.add(new annee(rs.getInt(1)));

        /* record=rs.getString(1);
        // Insert an element at the head of the list
        lannee.add(i, record);

        i++;
         * */

        }// fin du while
        connection.close();




        return lannee;
    }

//methode qui prend la liste des objet annees et retourne une liste des string des annees
    public static List<String> AvoiranneesStr(List<annee> lannee) {

        List<String> lanneeStr = new LinkedList<String>();
        Iterator it1 = lannee.iterator();
        //recuperer une liste des string des annees a partir du liste des objet anness
        while (it1.hasNext()) {
            // String anneeStr=null;
            annee a = (annee) it1.next();
            //anneeStr=Integer.toString(a.getAnnee());
            lanneeStr.add(Integer.toString(a.getAnnee()));
        }// end of while
        //afficher la liste des string
        Iterator it2 = lanneeStr.iterator();
        //System.out.println("+++++@DivertList.AvoirAnnee()++++les annees des la liste lanneeStr: ");
        while (it2.hasNext()) {

            System.out.println(it2.next());
        }

        return lanneeStr;
    }
//methode qui retourne la liste des centre

    public static List<centre> Avoircentres() throws SQLException {
        Connection connection = null;
        List<centre> lcentre = new LinkedList<centre>();    // Doubly-linked list
        // List implemented as growable array

        try {
            // Doubly-linked list
            // List implemented as growable array
            //    connection=getJdbcTables_isae().getConnection();
            connection = getJdbcIsae().getConnection();
        } catch (NamingException ex) {
            Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
        }

        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement("select IdCentre,NomCentre from Centre;");
        } catch (SQLException ex) {
            Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
        }
        ResultSet rs = ps.executeQuery();



        // int i=0;

        //System.out.println("++++@DivertList.Avoircentre()+++la liste des centres associer a section:");
        while (rs.next()) {
            lcentre.add(new centre(rs.getInt(1), rs.getString(2)));
             if(StaticClass.debug)System.out.println("+++++@DivertList.AvoirCentre()+++l'id du centre est: " + rs.getInt(1) + " ;le nom du centre est:" + rs.getString(2));

        // record=rs.getString(1);
        // Insert an element at the head of the list
        // lcentre.add(i, record);
        //i++;
        //System.out.println(record);
        }// fin du while
        connection.close();
        return lcentre;
    }
    //methode qui retourne le nom du centre en passant l'id

//v0
//methode qui retourne la liste des UeProf, les ues des tables section,matiere,enseignement,prof,enseignement.
    //  ue prof version 0,sans prendre dans la consideration le gradeaff
    public static List<UeProf> Avoirueprof(int annee, int IdCentre, int Nochef) throws SQLException {
        Connection connection = null;
        List<UeProf> lueprof = new LinkedList<UeProf>();
        try {
            //  connection=getJdbcTables_isae().getConnection();
            connection = getJdbcIsae().getConnection();
        } catch (NamingException ex) {
            Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
        }
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement("SELECT t1.noenseig,t2.Nomatiere,t3.intitule,t4.numprof,t4.prenomprof,t4.nomprof,t2.NoOuverture,t2.NoGroupe,t2.NoSection,t3.cnam from Enseignement as t1,Section as  t2,Matiere as t3,Professeur as t4 where t4.numprof=t1.numprof and t1.NoSection=t2.NoSection and t3.Nomatiere=t2.Nomatiere and Nochef=? and annee=? and IdCentre=?");
        } catch (SQLException ex) {
            Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
        }
        //converting types
        String NochefStr = Integer.toString(Nochef);
        String anneeStr = Integer.toString(annee);
        String IdCentreStr = Integer.toString(IdCentre);
        //setting values
        ps.setString(1, NochefStr);
        ps.setString(2, anneeStr);
        ps.setString(3, IdCentreStr);

        ResultSet rs = ps.executeQuery();
        System.out.println("");
        while (rs.next()) {
            //UeProf(int noenseig,int Nomatiere,String intitule,int numprof,String prenomprof,String nomprof,int NoOuverture,int NoGroupe,int NoSection,String cnam)
            lueprof.add(new UeProf(rs.getInt(1), rs.getInt(2), rs.getString(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getInt(7), rs.getInt(8), rs.getInt(9), rs.getString(10)));
        //System.out.println( rs.getString(3)+"--"+rs.getString(5)+"-"+rs.getString(6));

        }
        connection.close();
        return lueprof;
    }  // fin de la methode avoirueprof

    public static String Avoirnomcentre(int centre) throws SQLException {
        Connection connection = null;
        String nomcentre = null;
        try {
            //  connection=getJdbcTables_isae().getConnection();
            connection = getJdbcIsae().getConnection();
        } catch (NamingException ex) {
            Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
        }
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement("select NomCentre from Centre where IdCentre=? ");
        } catch (SQLException ex) {
            Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
        }
        String centreStr = Integer.toString(centre);
        ps.setString(1, centreStr);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            nomcentre = rs.getString(1);
            if(StaticClass.debug)System.out.println("+++++@DivertList.AvoirnomCentre()+++++le nom du centre est:" + nomcentre);
        }


        connection.close();
        return nomcentre;
    }

    public static String AvoirIntitule(int noenseig) throws SQLException {
        Connection connection = null;
        String intitule = null;


        try {
            //  connection=getJdbcTables_isae().getConnection();
            connection = getJdbcIsae().getConnection();
        } catch (NamingException ex) {
            Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
        }
        PreparedStatement ps = connection.prepareStatement("select t3.intitule from Enseignement as t1,Section as t2,Matiere as t3 where t1.NoSection=t2.NoSection and t2.Nomatiere=t3.Nomatiere and noenseig=?");
        String noenseigStr = Integer.toString(noenseig);
        ps.setString(1, noenseigStr);
        ResultSet rs = ps.executeQuery();

        //System.out.println("++++@DivertList.AvoirIntitule(noenseig)+++l'intitule associer au noenseig: " + noenseig + " est la suivante:");
        while (rs.next()) {
            intitule = rs.getString(1);
            System.out.println(intitule);
        }
        connection.close();
        return intitule;
    }

    public static List<spec> AvoirSpecs(int Nochef) throws SQLException {
        Connection connection = null;
        List<spec> lspec = new LinkedList<spec>();    // Doubly-linked list
        // List implemented as growable array
        try {
            // Doubly-linked list
            // List implemented as growable array
            //  connection=getJdbcTables_isae().getConnection();
            connection = getJdbcIsae().getConnection();
        } catch (NamingException ex) {
            Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
        }
        PreparedStatement ps = connection.prepareStatement("SELECT t1.spec,t1.specialite,t2.NoChef,t2.Nomch,t2.Prenomch FROM Specialite as t1,Chef as t2 where t1.NoChef=t2.NoChef and t2.Nochef=? and t1.anfin is null ");
        ps.setInt(1, Nochef);
        ResultSet rs = ps.executeQuery();

        //System.out.println("++++@DivertList.AvoirSpec()+++la liste des spec :");
        while (rs.next()) {
            lspec.add(new spec(rs.getInt("spec"), rs.getString("specialite"), rs.getInt("NoChef"), rs.getString("Nomch"), rs.getString("Prenomch")));
        //System.out.println("+++++@DivertList.AvoirSpec()+++" + rs.getInt("spec") + "-" + rs.getString("specialite") + "-" + rs.getInt("NoChef") + "-" + rs.getString("Nomch") + "-" + rs.getString("Prenomch"));
        }
        connection.close();
        return lspec;
    }


    // v0-------------------------------------------------------------------
    public static List<spec> AvoirSpecsTotal() throws SQLException {
        Connection connection = null;
        List<spec> lspec = new LinkedList<spec>();    // Doubly-linked list
        // List implemented as growable array
        try {
            // Doubly-linked list
            // List implemented as growable array
            //  connection=getJdbcTables_isae().getConnection();
            connection = getJdbcIsae().getConnection();
        } catch (NamingException ex) {
            Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
        }
        PreparedStatement ps = connection.prepareStatement("SELECT t1.spec,t1.specialite,t2.NoChef,t2.Nomch,t2.Prenomch FROM Specialite as t1,Chef as t2 where t1.NoChef=t2.NoChef and t1.anfin is null ");

        ResultSet rs = ps.executeQuery();

        //System.out.println("++++@DivertList.AvoirSpec()+++la liste des spec :");
        while (rs.next()) {
            lspec.add(new spec(rs.getInt("spec"), rs.getString("specialite"), rs.getInt("NoChef"), rs.getString("Nomch"), rs.getString("Prenomch")));
        //System.out.println("+++++@DivertList.AvoirSpec()+++" + rs.getInt("spec") + "-" + rs.getString("specialite") + "-" + rs.getInt("NoChef") + "-" + rs.getString("Nomch") + "-" + rs.getString("Prenomch"));
        }
        connection.close();
        return lspec;
    }

    //v0
    //avoir gradeaff du table affectation en passant en parametre annee,centre,spec
    public static List<gradeaff> AvoirGradeaffs(int annee, int IdCentre, int spec) throws SQLException {
        Connection connection = null;
        List<gradeaff> lgradeaff = new LinkedList<gradeaff>();    // Doubly-linked list
        // List implemented as growable array


        try {
            //   connection=getJdbcTables_isae().getConnection();
            connection = getJdbcIsae().getConnection();
        } catch (NamingException ex) {
            Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
        }
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement("select distinct gradeaff from Affectation where annee=? and IdCentre=? and spec=? order by gradeaff; ");
        } catch (SQLException ex) {
            Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
        }
        ps.setInt(1, annee);
        ps.setInt(2, IdCentre);
        ps.setInt(3, spec);
        ResultSet rs = ps.executeQuery();

        //System.out.println("++++@DivertList.AvoirGradeaff()+++la liste des Gradaff :");
        while (rs.next()) {
            lgradeaff.add(new gradeaff(rs.getInt(1)));
        //System.out.println("+++++@DivertList.AvoirGradeaff(annee,spec,centre)+++ " + rs.getInt(1));
        }
        connection.close();
        return lgradeaff;
    }
//v1 

    public static List<gradeaff> AvoirGradeafftotal() throws SQLException {
        Connection connection = null;
        List<gradeaff> gradeaffL = new LinkedList<gradeaff>();    // Doubly-linked list
        // List implemented as growable array


        try {
            //     connection=getJdbcTables_isae().getConnection();
            connection = getJdbcIsae().getConnection();
        } catch (NamingException ex) {
            Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
        }
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement("select distinct gradeaff from Affectation order by gradeaff; ");
        } catch (SQLException ex) {
            Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
        }
        ResultSet rs = ps.executeQuery();

        //System.out.println("++++@DivertList.AvoirGradeaff()+++la liste des Gradafftotal :");
        while (rs.next()) {
            gradeaffL.add(new gradeaff(rs.getInt(1)));
        //System.out.println("+++++@DivertList.AvoirGradeaff()+++ " + rs.getInt(1));
        }
        connection.close();
        return gradeaffL;



    }

//v1
    //UeProf version 1  en tenant compte du spec et du gradeaff dans la requete,la structure de l'objet ueprof est le meme
    //v2.1 --jour de type string
    //avoir les matieres -prof
    //ue prof par p,j
    public static List<UeProf> AvoirMP1(int annee, int IdCentre, int spec, int gradeaffL[], int p, int j) throws SQLException {
        Connection connection = null;
        List<UeProf> mpl = new LinkedList<UeProf>();
        //etape-1----
        String query = "select distinct t1.noenseig,t2.Nomatiere,t3.intitule,t4.numprof,t4.prenomprof,t4.nomprof,t2.NoOuverture,t2.NoGroupe, t2.NoSection,t3.cnam";
        query += " from Enseignement as t1, Section as t2, Matiere as t3, Professeur as t4, Affectation as t5,HoraireHebdo as t6 ";
        query += " where t4.numprof=t1.numprof and t1.NoSection=t2.NoSection and t3.Nomatiere=t2.Nomatiere ";
        query += " and t2.Nomatiere=t5.Nomatiere and t2.annee=t5.annee and t2.IdCentre=t5.IdCentre and ";
        query += " t2.NoOuverture=t5.NoOuverture  and t1.noenseig=t6.noenseig and t5.annee=" + annee + " and t5.IdCentre=" + IdCentre + " and t5.spec=" + spec;
        // query+=" and t6.Jour=1 and t6.PeriodeH=1 and (";


        // 2---ajout/recuperation du :  and t6.jour="lundi"
        switch (j) {
            case 1:
                query += " and t6.Jour='lundi' ";
                break;
            case 2:
                query += " and t6.Jour='mardi' ";
                break;
            case 3:
                query += " and t6.Jour='mercredi' ";
                break;
            case 4:
                query += " and t6.Jour='jeudi' ";
                break;
            case 5:
                query += " and t6.Jour='vendredi' ";
                break;
            case 6:
                query += " and t6.Jour='samedi' ";
                break;
        }
        //3----ajout au requete la periodeH
        switch (p) {
            case 1:
                query += " and t6.PeriodeH=1 ";
                break;
            case 2:
                query += " and t6.PeriodeH=2 ";
                break;
            case 3:
                query += " and t6.PeriodeH=3 ";
                break;
        }
        //4----ajout du and (
        query += " and ( ";

        //5-----recuperation du gradeaff
        int ind = 0;//indicateur utilise pour  pour le premier element
        //System.out.println("+++++divertlist++avoirMP1=gradeaff.length:" + gradeaffL.length);//+++++divertlist++=gradeaff.length:2

        for (int i = 0; i < gradeaffL.length; i++) {
            //System.out.println("+++++divertlist++=gradeaff[" + i + "]:" + gradeaffL[i]);
            //si on a un seul element gradeaff
            if (gradeaffL.length == 1) {
                query += "t5.gradeaff=" + gradeaffL[i];
            } //si on a une chaine d'element gradeaff,l'algo:
            else if (gradeaffL.length > 1) {
                switch (ind) {
                    //concatener la  premier chaine
                    case 0:
                        query += "t5.gradeaff=" + gradeaffL[i];
                        ind = 1;
                        break;
                    //pour les autres chaines
                    case 1:
                        query += " or t5.gradeaff=" + gradeaffL[i];
                        break;
                }//fin du switch
            }//fin de grad.lengh
        }//fin du for

        //reuperation de la fin di chaine
        query += ");";
        //System.out.println("+++divertList++++avoir MP+++la requete recuperer:" + query);

        //etape::: connection et execution de la requete
        try {
            connection = getJdbcTables_isae().getConnection();
        // connection = getJdbcIsae().getConnection();
        //System.out.print("++++divertlist+++avoirMP+++connection etabi");
        } catch (NamingException ex) {
            Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
        }
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(query);

        } catch (SQLException ex) {
            Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
        }
        ResultSet rs = ps.executeQuery();

        while (rs.next()) {
            //UeProf(int noenseig,int Nomatiere,String intitule,int numprof,String prenomprof,String nomprof,int NoOuverture,int NoGroupe,int NoSection,String cnam)
            mpl.add(new UeProf(rs.getInt("noenseig"), rs.getInt("Nomatiere"), rs.getString("intitule"), rs.getInt("numprof"), rs.getString("prenomprof"), rs.getString("nomprof"), rs.getInt("NoOuverture"), rs.getInt("NoGroupe"), rs.getInt("NoSection"), rs.getString("cnam")));
        //System.out.println("++++++ @DivertList.avoirMP+++++ l'intitule:" + rs.getString("intitule") + " prenomprof:" + rs.getString("prenomprof") + "nomprof: " + rs.getString("nomprof"));
        // ++ @DivertListmn.avoirueprofgf+++++ l'intitule,le prof retourner par la requete sont ajoutees dans lueprofgf :Mémoire d'ingénieur électrotechnique--khatib-ahmad

        }
        connection.close();
        return mpl;
    }//fin de avoir avoiMP1

    //v2.2---jour periode integer
    //ue prof par p,j,insert au DB jour :int,ajout du langue et matspec pour pouvoir sepaerer entre eux
    public static List<UeProf> AvoirMP2(int annee, int IdCentre, int spec, int gradeaffL[], int p, int j, int langue, int matspec) throws SQLException {
        Connection connection = null;
        List<UeProf> mpl = new LinkedList<UeProf>();
        //etape-1----
        if (gradeaffL != null) {
            String query = "select distinct t1.noenseig,t2.Nomatiere,t3.intitule,t4.numprof,t4.prenomprof,t4.nomprof,t2.NoOuverture,t2.NoGroupe, t2.NoSection,t3.cnam";
            query += " from Enseignement as t1, Section as t2, Matiere as t3, Professeur as t4, Affectation as t5,HoraireHebdo as t6 ";
            query += " where t4.numprof=t1.numprof and t1.NoSection=t2.NoSection and t3.Nomatiere=t2.Nomatiere ";
            query += " and t2.Nomatiere=t5.Nomatiere and t2.annee=t5.annee and t2.IdCentre=t5.IdCentre and ";
            query += " t2.NoOuverture=t5.NoOuverture  and t1.noenseig=t6.noenseig and t5.annee=" + annee + " and t5.IdCentre=" + IdCentre + " and t5.spec=" + spec;
            query += " and t6.Jour=" + j + " and t6.PeriodeH=" + p;
            query += " and (t3.langue mod 2 =" + langue + " or t3.langue mod 2 =" + matspec + ") ";


            //4----ajout du and (
            query += " and ( ";

            //5-----recuperation du gradeaff
            int ind = 0;//indicateur utilise pour le premier element
            //System.out.println("+++++divertlist++=gradeaff.length:" + gradeaffL.length);//+++++divertlist++=gradeaff.length:2
            //=============> C'est quoi cette boucle????????? =============
            for (int i = 0; i < gradeaffL.length; i++) {
                //System.out.println("+++++divertlist++=gradeaff[" + i + "]:" + gradeaffL[i]);//+++++divertlist++=gradeaff.length:2
                //si on a un seul element gradeaff
                if (gradeaffL.length == 1) {
                    query += "t5.gradeaff=" + gradeaffL[i];
                } //si on a une chaine d'element gradeaff,l'algo:
                else if (gradeaffL.length > 1) {
                    switch (ind) {
                        //concatener la  premier chaine
                        case 0:
                            query += "t5.gradeaff=" + gradeaffL[i];
                            ind = 1;
                            break;
                        //pour les autres chaines
                        case 1:
                            query += " or t5.gradeaff=" + gradeaffL[i];
                            break;
                    }//fin du switch
                }//fin de grad.lengh
            }//fin du for

            //reuperation de la fin di chaine
            query += ");";
            if(pack1.StaticClass.debug)System.out.println("+++divertList++++avoirMP2+++la requete recuperer:" + query);

            //etape::: connection et execution de la requete
            try {
                // connection=getJdbcTables_isae().getConnection();
                connection = getJdbcIsae().getConnection();
            //System.out.print("++++divertlist+++avoirMP+++connection etabi");
            } catch (NamingException ex) {
                Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
            }
            PreparedStatement ps = null;
            try {
                ps = connection.prepareStatement(query);

            } catch (SQLException ex) {
                Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
            }
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                //UeProf(int noenseig,int Nomatiere,String intitule,int numprof,String prenomprof,String nomprof,int NoOuverture,int NoGroupe,int NoSection,String cnam)
                mpl.add(new UeProf(rs.getInt("noenseig"), rs.getInt("Nomatiere"), rs.getString("intitule"), rs.getInt("numprof"), rs.getString("prenomprof"), rs.getString("nomprof"), rs.getInt("NoOuverture"), rs.getInt("NoGroupe"), rs.getInt("NoSection"), rs.getString("cnam")));
            //System.out.println("++++++ @DivertList.avoirMP+++++ l'intitule:" + rs.getString("intitule") + " prenomprof:" + rs.getString("prenomprof") + "nomprof: " + rs.getString("nomprof"));
            // ++ @DivertListmn.avoirueprofgf+++++ l'intitule,le prof retourner par la requete sont ajoutees dans lueprofgf :Mémoire d'ingénieur électrotechnique--khatib-ahmad

            }
            connection.close();
        }//end if gradeaffL not null
        return mpl;
    }//fin de avoir avoiMP

//UePorf version 3.1 ajout de la condition langue-10-3-09,matspec pour separer entre eux
    public static List<UeProf> AvoirUeProfGdL(int annee, int IdCentre, int spec, int gradeaffL[], int langue, int matspec) throws SQLException {
        Connection connection = null;
        List<UeProf> mpl = new LinkedList<UeProf>();
        //if(annee!=0 && IdCentre !=0 && spec !=0){
        //etape-1----
        if (gradeaffL != null) {
            String query = "select distinct t1.noenseig,t2.Nomatiere,t2.langue,t3.intitule,t4.numprof,t4.prenomprof,t4.nomprof,t2.NoOuverture,t2.NoGroupe, t2.NoSection,t3.cnam";
            query += " from Enseignement as t1, Section as t2, Matiere as t3, Professeur as t4, Affectation as t5";
            query += " where t4.numprof=t1.numprof and t1.NoSection=t2.NoSection and t3.Nomatiere=t2.Nomatiere ";
            query += " and t2.Nomatiere=t5.Nomatiere and t2.annee=t5.annee and t2.IdCentre=t5.IdCentre and ";
            query += " t2.NoOuverture=t5.NoOuverture and t5.annee=" + annee + " and t5.IdCentre=" + IdCentre + " and t5.spec=" + spec;
            query += " and (t3.langue mod 2 =" + langue + " or t3.langue mod 2 =" + matspec + ") ";
            //4----ajout du and (
            query += " and ( ";

            //5-----recuperation du gradeaff
            int ind = 0;//indicateur utilise pour  pour le premier element
            //System.out.println("+++++divertlist++avoirUeProfGdL=gradeaff.length:" + gradeaffL.length);//+++++divertlist++=gradeaff.length:2

            for (int i = 0; i < gradeaffL.length; i++) {

                //si on a un seul element gradeaff
                if (gradeaffL.length == 1) {
                    query += "t5.gradeaff=" + gradeaffL[i];
                } //si on a une chaine d'element gradeaff,l'algo:
                else if (gradeaffL.length > 1) {
                    switch (ind) {
                        //concatener la  premier chaine
                        case 0:
                            query += "t5.gradeaff=" + gradeaffL[i];
                            ind = 1;
                            break;
                        //pour les autres chaines
                        case 1:
                            query += " or t5.gradeaff=" + gradeaffL[i];
                            break;
                    }//fin du switch
                }//fin de grad.lengh
            }//fin du for

            //reuperation de la fin di chaine
            query += ");";
             if(StaticClass.debug)System.out.println("+++divertList++++avoir UeProfGrdL+++la requete recuperer:" + query);

            //etape::: connection et execution de la requete
            try {
                //  connection=getJdbcTables_isae().getConnection();
                connection = getJdbcIsae().getConnection();
                 if(StaticClass.debug)System.out.print("++++divertlist+++avoirueprofGDL+++connection etabi");
            } catch (NamingException ex) {
                Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
            }
            PreparedStatement ps = null;
            try {
                ps = connection.prepareStatement(query);

            } catch (SQLException ex) {
                Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
            }
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                //UeProf(int noenseig,int Nomatiere,String intitule,int numprof,String prenomprof,String nomprof,int NoOuverture,int NoGroupe,int NoSection,String cnam)
               // mpl.add(new UeProf(rs.getInt("noenseig"), rs.getInt("Nomatiere"), rs.getString("intitule"), rs.getInt("numprof"), rs.getString("prenomprof"), rs.getString("nomprof"), rs.getInt("NoOuverture"), rs.getInt("NoGroupe"), rs.getInt("NoSection"), rs.getString("cnam")));
                 mpl.add(new UeProf(rs.getInt("noenseig"), rs.getInt("Nomatiere"), rs.getString("intitule"), rs.getInt("numprof"), rs.getString("prenomprof"), rs.getString("nomprof"), rs.getInt("NoOuverture"), rs.getInt("NoGroupe"), rs.getInt("NoSection"), rs.getString("cnam"),rs.getString("langue")));
                if(StaticClass.debug)System.out.println("++++++ @DivertList.avoirueprofGDL+++++ l'intitule:" + rs.getString("intitule") + " prenomprof:" + rs.getString("prenomprof") + "nomprof: " + rs.getString("nomprof")+"/"+rs.getString("langue"));
            // ++ @DivertListmn.avoirueprofgf+++++ l'intitule,le prof retourner par la requete sont ajoutees dans lueprofgf :Mémoire d'ingénieur électrotechnique--khatib-ahmad

            }
            connection.close();
        }//end gradeaffL not null
        return mpl;

    }//fin de avoir avoiueprofgdl

    //version 01 --inserer une seul tuple avec une seul connection
    public static String InsertHorairehebdo2(int noenseig, int Jour, int PeriodeH) throws SQLException {
        String msg = " ";
        if (noenseig != 0) {
            Connection connection = null;
            int rowaffected = 0;

            Statement stmt = null;
            String query = "select * from HoraireHebdo where noenseig=" + noenseig;
            query += " and Jour=" + Jour + " and PeriodeH=" + PeriodeH + ";";
            System.out.println("+++++Diverlist++inserthoraireJourInt++debut methode....");
            System.out.println(query);
            try {
                //  connection=getJdbcTables_isae().getConnection();
                connection = getJdbcIsae().getConnection();
                stmt = connection.createStatement();
            } catch (NamingException ex) {
                Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
            }


            ResultSet rs = stmt.executeQuery(query);
            if (rs.first() == false) {
                //System.out.println(".............pas de record,ce horaire n'existe pas on peut l'inserer");
                String queryIns = "insert into HoraireHebdo(noenseig,Jour,PeriodeH) values(" + noenseig + "," + Jour + "," + PeriodeH + ");";
                //System.out.println("+++divertlist++inserthoraire+ query recuperer: " + queryIns);
                rowaffected = stmt.executeUpdate(queryIns);
                connection.close();

                if (rowaffected == 1) {
                    //System.out.print("++++DivertList.inserthorairehebo(noenseig,jour,periodeh) is executed++++the row is affected\n");
                    msg = " row affected successfully ";
                //System.out.print("++++DivertList.inserthoraireheboJourint:" + msg);
                } else {
                    msg = "Error---row not effected";
                //System.out.print("++++DivertList.inserthoraireheboJourint:" + msg);
                }


            }//end rs.first
            else {

                msg = "le tuple existe";
            //System.out.print("++++DivertList.inserthoraireheboJourint:" + msg);
            }

        }//fin de if noenensig not zero
        else {
            msg = "noenseig =0";
        }
        return msg;
    }//fin de insert horaire hebdo Jour integer (un seul tuple)


//si le l'horaireexiste
    //
    public static boolean SiHoraireExiste(int noenseig, int Jour, int PeriodeH) throws SQLException {
        Connection connection = null;
        boolean res;

        String query = "select * from HoraireHebdo where noenseig=" + noenseig;
        query += " and Jour=" + Jour + " and PeriodeH=" + PeriodeH + ";";
        //System.out.println("+++++Diverlist++SiHoraireExiste++debut methode....");
        try {
            //      connection=getJdbcTables_isae().getConnection();
            connection = getJdbcIsae().getConnection();

        } catch (NamingException ex) {
            Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
        }
        //System.out.println("++++divertlist++siHoraireExiste+++la connection etabli,la requet recup:" + query);
        PreparedStatement ps = null;
        ps = connection.prepareStatement(query);
        ResultSet rs = ps.executeQuery();
        if (rs.first() == false) {
            if(StaticClass.debug)System.out.println("++++divertlist++siHoraireExiste+++l'horaire n'existe pas");
            res = false;
        } else {
            if(StaticClass.debug)System.out.println("++++divertlist++siHoraireExiste+++l'horaire existe ");
            res = true;
        }
        connection.close();

        return res;
    }//fin de si l'horaire existe


    //methode qui retourne l'horaire total comme une list globale
    public static List<horairehebdo> Avoirhorairehebdototal() throws SQLException {
        Connection connection = null;
        List<horairehebdo> horairehebdototal = new LinkedList<horairehebdo>();
        //initialization de la varalble globale horairehebdototal
        // horairehebdototal.clear();

        if (horairehebdototal != null) {
            //si la list est initializer avec succes
            if (horairehebdototal.size() == 0) {
                try {
                    // connection=getJdbcTables_isae().getConnection();
                    connection = getJdbcIsae().getConnection();
                } catch (NamingException ex) {
                    Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
                }
                PreparedStatement ps = null;
                try {
                    //ce n'est pas logique d'appeler tout l'horaire pour un groupe de noenseig
                    ps = connection.prepareStatement("select t1.noenseig,t1.IdSalle,t1.Jour,t1.PeriodeH,t4.Nochef from HoraireHebdo as t1,Enseignement as t2,Section as t3,Matiere as t4 where t1.noenseig=t2.noenseig and t2.NoSection=t3.NoSection and t3.Nomatiere=t4.Nomatiere; ");
                } catch (SQLException ex) {
                    Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
                }
                ResultSet rs = null;
                try {
                    rs = ps.executeQuery();
                } catch (SQLException ex) {
                    Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
                }
                //System.out.println("");
                while (rs.next()) {  //recuperation de la list horaire hebdo
                    horairehebdototal.add(new horairehebdo(rs.getInt("noenseig"), rs.getInt("Jour"), rs.getInt("PeriodeH"), rs.getInt("Nochef")));
                //System.out.println("++++++ @DivertList.Avoirehorairehebdototal()::liste horaire total (lire le table horairehebdo completement),noenseig:" + rs.getInt("noenseig") + "IdSalle:" + rs.getInt("IdSalle") + "Jour:" + rs.getString("Jour") + "PeriodeH: " + rs.getInt("PeriodeH"));
                }
                connection.close();
            } else if (horairehebdototal.size() > 0) {
                //System.out.println("++++@Divertlist--avoirhorairetotal----la list horairetotal n'est pas initializer elle contient des element residues");
            }
        } else if (horairehebdototal == null) {
            //System.out.println("++++@Divertlist--avoirhorairetotal----la list horairetotal est null");
        }
        return horairehebdototal;
    }

    //methode qui prend la list des noenseig pour un gradeaff donnee resultant de ueprofgradeaff(...)
    //et retourne la list des horairehebdos qui correspond a la liste des noenseigs
    public static List<horairehebdo> Avoirhorairehebdo(List<noenseig> noenseiggfl, List<horairehebdo> horairehebdototal) throws SQLException {

        if (!noenseiggfl.isEmpty()) {
            //filtrage horairehebdo ,on choisie l'horaire pour un gradeaff cad pour l'ensenble des noenseigs
            List<horairehebdo> resultat = new LinkedList<horairehebdo>();

            //si les noenseiggfl retourner par ueprof n'est pas vide on fait la comparaison entre cette liste et l'horairehebdo
            if (noenseiggfl != null) {
                for (noenseig nse : noenseiggfl) {
                    for (horairehebdo hrh : horairehebdototal) {
                        if (nse.getNoenseig() == hrh.getNoenseig()) {
                            //on affecte aussi le nom,prenom du prof,l'intitule au horaire correspondant
                            hrh.setPrenomprof((nse.getPrenomprof()));
                            hrh.setNomprof(nse.getNomprof());
                            String prnmpf = nse.getPrenomprof() + " " + nse.getNomprof();
                            hrh.setPrenomnomprof(prnmpf);
                            hrh.setIntitule(nse.getIntitule());
                            hrh.setNumprof(nse.getNumprof());

                            resultat.add(hrh);
                        //System.out.println("+++++++ @DivertList.avoirhorairehebdo(noenseiggfl++++l'horaire ajouter au resultat(list):" + hrh.getIntitule() + " " + hrh.getPrenomprof() + " " + hrh.getNomprof() + "le prenonomprof: :" + hrh.getPrenomnomprof() + " " + hrh.getJour() + " " + hrh.getPeriodeH());
                        } else {
                            //System.out.println("+++++++DivertList.Avoirhorairehebdo(noenseiggfl,horairetotal)+++ cette valeur noenseig: " + hrh.getNoenseig() + " de horairehebdo list est different du noenseig: " + nse.getNoenseig() + " passer en parametre: ");
                        }
                    }
                }
            }//fin de if est empty
            //si la list noenseiggfl est vide on affiche un message
            else {
                //System.out.println("+++++++ @DivertList.avoirhorairehebdo(noenseiggfl)+++ la liste des noenseig est vide ,pas des matieres attachees a ce gradeaff");
            }

            //affichage de la liste resultat


            if (resultat.isEmpty()) {
                if(StaticClass.debug)System.out.println("++++++DiverList.avoirhorairehebdo(noenseiggfl)++++ l'horaire est vide,La resultat est vide aucun valeur dans horaire hebdo correspond au noenseig passe en parametre");
            } else {
                for (horairehebdo h : resultat) {
                    if(StaticClass.debug)System.out.println("++++++ @DivertList.Avoirehorairehebdo(List<noenseig>)::liste horaire apres filtrage avec noenseig passees en parametre d'horaire par noenseig:" + h.getNoenseig() + "IdSalle:" + h.getIdSalle() + "Jour:" + h.getJour() + "PeriodeH: " + h.getPeriodeH());
                }

            }//fin du else
            return resultat;
        }//fin de if si noenseig n'est pas empty
        //si noenseig est null
        else {
            System.out.println("++++++ @DivertList.Avoirehorairehebdo(listnoenseiggfl):la liste noenseig est null ou empty----620 ");
            return null;

        }
    }//fin de la methode
//version 0, supprimer un seul tuple --jour de type string

    public static String DeleteHorairehebdo1(int noenseig, int IdSalle, String Jour, int PeriodeH) throws SQLException {
        Connection connection = null;
        int rowaffected = 0;
        String msg = "";
        try {
            //   connection=getJdbcTables_isae().getConnection();
            connection = getJdbcIsae().getConnection();
        } catch (NamingException ex) {
            Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
        }
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement("delete from HoraireHebdo where noenseig=? and IdSalle=? and Jour=? and PeriodeH=? ;");
        } catch (SQLException ex) {
            Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
        }
        // The driver converts this to an SQL INTEGER value when it sends it to the database.an SQL INTEGER value when it sends it to the databas
        ps.setInt(1, noenseig);
        ps.setInt(2, IdSalle);
        ps.setString(3, Jour);
        ps.setInt(4, PeriodeH);

        rowaffected = ps.executeUpdate();
        connection.close();
        if (rowaffected == 1) {
            msg = "DivertList-deletehorairehebdo-1 row deleted";
        } else if (rowaffected == 0) {
            msg = "Divertlist-deletehorairehebdo-0 row deleted";
        }
        return msg;

    }

    //version 01 supprimer un horaire ou le jour est de type int
    //la salle est supprimer
    public static String DeleteHorairehebdo2(int noenseig, int Jour, int PeriodeH) throws SQLException {
        try {
            Connection connection = null;
            int rowaffected = 0;
            String msg = "";
            //  connection=getJdbcTables_isae().getConnection();
            connection = getJdbcIsae().getConnection();
            PreparedStatement ps = null;
            ps = connection.prepareStatement("delete from HoraireHebdo where noenseig=? and Jour=? and PeriodeH=? ;");
            // The driver converts this to an SQL INTEGER value when it sends it to the database.an SQL INTEGER value when it sends it to the databas
            ps.setInt(1, noenseig);
            // ps.setInt(2, IdSalle);
            ps.setInt(2, Jour);
            ps.setInt(3, PeriodeH);
            rowaffected = ps.executeUpdate();
            connection.close();
            if (rowaffected == 1) {
                msg = "DivertList-deletehorairehebdo-1 row deleted";
            } else if (rowaffected == 0) {
                msg = "Divertlist-deletehorairehebdo-0 row deleted";
            }
            //System.out.println("++divertlist++inserthoraire2++" + msg);
            return msg;
        } catch (NamingException ex) {
            Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
        }
        return "ERREUR:deletehorairehebdo2(..)";

    }

//version 1: supprimer une list des tuples d'horaire
    public static String DeleteHoraireHebdoList(List<horairehebdo> horairehebdodel) throws SQLException {
        Connection connection = null;
        //transaction
        int rowdeleted = 0;
        String msg = "";
        Statement stmt = null;
        String query = "delete from HoraireHebdo where ";
        try {
            //       connection=getJdbcTables_isae().getConnection();
            connection = getJdbcIsae().getConnection();
            stmt = connection.createStatement();
        } catch (NamingException ex) {
            Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
        }

        stmt.execute("BEGIN; ");

        for (horairehebdo hr : horairehebdodel) {
            query += "noenseig=" + hr.getNoenseig() + " and IdSalle=" + hr.getIdSalle() + " and Jour='" + hr.getJour() + "' and PeriodeH=" + hr.getPeriodeH() + ";";
            rowdeleted += stmt.executeUpdate(query);
            if(StaticClass.debug)System.out.println("++++divertlist ++deletehorairelist+le requete concatener" + query);
            query = "delete from HoraireHebdo where ";

        }

        stmt.execute("COMMIT;");

        //System.out.println("+++Divertlist-deletehorairelist::row deleted:" + rowdeleted);
        msg = "row deleted:" + rowdeleted;
        return msg;
    }

    //version 0
    public static boolean Sirespmatieredb(int Nochef, int noenseig) throws SQLException {
        Connection connection = null;

        try {

            //    connection=getJdbcTables_isae().getConnection();
            connection = getJdbcIsae().getConnection();
            PreparedStatement ps = connection.prepareStatement("SELECT t1.Nomatiere,t1.intitule from Matiere as t1,Enseignement as t2,Section as t3 where t2.NoSection=t3.NoSection and t3.Nomatiere=t1.Nomatiere and t2.noenseig=? and t1.Nochef=?;");
            // The driver converts this to an SQL INTEGER value when it sends it to the database.an SQL INTEGER value when it sends it to the databas
            ps.setInt(1, noenseig);
            ps.setInt(2, Nochef);
//
            ResultSet rs = ps.executeQuery();
            if (rs.first()) {

                return true;
            } else {
                return false;
            }
        } catch (NamingException ex) {

            Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    //version 1
    public static boolean Sirespmatiere(int Nochef, int NochefH) {
        boolean res = false;
        if (Nochef == NochefH) {
            res = true;
        } else if (Nochef != NochefH) {
            res = false;
        }

        return res;
    }

    //avoir noenseig chef
    public static List<NoenseigChef> AvoirNoenseigChef() throws SQLException {
        Connection connection = null;
        List<NoenseigChef> noenseigchefl = new LinkedList<NoenseigChef>();    // Doubly-linked list
        // List implemented as growable array
        try {
            // Doubly-linked list
            // List implemented as growable array
            //  connection=getJdbcTables_isae().getConnection();
            connection = getJdbcIsae().getConnection();
        } catch (NamingException ex) {
            Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
        }
        PreparedStatement ps = connection.prepareStatement("select t1.noenseig,t3.Nochef from Enseignement as t1,Section as t2,Matiere as t3 where t1.NoSection=t2.NoSection and t2.Nomatiere=t3.Nomatiere; ");
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            noenseigchefl.add(new NoenseigChef(rs.getInt("noenseig"), rs.getInt("Nochef")));
        //  System.out.println("+++++@DivertList.AvoirNoenseigchef()+++le noenseig est: " + rs.getInt(1) + " ;la Nochef est:" + rs.getInt(2));
        }
        connection.close();
        return noenseigchefl;

    }

    public static boolean SiNoenseigChef(int Nochef, int noenseig, List<NoenseigChef> noenseigchefl) {
        boolean res = false;
        // System.out.println("+++++Divertlist++sinoenseigchef le chef passe en param:" + Nochef + ",le noenseig:" + noenseig);
        for (NoenseigChef ec : noenseigchefl) {
            // System.out.println("+++++Divertlist++sinoenseigchef la list noenseig chef:" + ec.getNochef() + ",le noenseig:" + ec.getNoenseig());
        }

        for (NoenseigChef ec : noenseigchefl) {
            if (Nochef == ec.getNochef() && noenseig == ec.getNoenseig()) {
                //  System.out.println("+++++Divertlist++sinoenseigchef--le chef du liste:" + ec.getNochef() + "existe pour le noenseig" + ec.getNoenseig());
                res = true;
            }
        }
        return res;
    }

    public static List<Professeur> AvoirProfesseurs() throws SQLException {
        Connection connection = null;
        List<Professeur> profl = new LinkedList<Professeur>();
        try {
            //    connection=getJdbcTables_isae().getConnection();
            connection = getJdbcIsae().getConnection();
        } catch (NamingException ex) {
            Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
        }
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement("select numprof,prenomprof,nomprof from Professeur;");
        } catch (SQLException ex) {
            Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
        }
        ResultSet rs = ps.executeQuery();
        int i = 0;
        while (rs.next()) {
            i++;
            profl.add(new Professeur(rs.getInt("numprof"), rs.getString("prenomprof"), rs.getString("nomprof")));
        }// fin du while
        connection.close();
        if(StaticClass.debug)System.out.println("+++++ @divertlist-avoirprofesseur...le nombre des professeurs extraient: " + i);
        return profl;
    }

    public static Chef AvoirChefdetail(int nochef) throws SQLException {
        Connection connection = null;
        Chef chef = new Chef();
        try {
            //    connection=getJdbcTables_isae().getConnection();
            connection = getJdbcIsae().getConnection();
        } catch (NamingException ex) {
            Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
        }
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement("select * from Chef where Nochef=? ;");
            ps.setInt(1, nochef);

        } catch (SQLException ex) {
            Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
        }
        ResultSet rs = ps.executeQuery();
        if(StaticClass.debug)System.out.println("+++++ @divertlist-avoir chef detail,la requete est executee");
        while (rs.next()) {
            chef.setNochef(nochef);
            chef.setNomchef(rs.getString("Nomch"));
            chef.setNomchef(rs.getString("Prenomch"));
        }

        connection.close();
        if(StaticClass.debug)System.out.println("+++++ @divertlist-avoir chef detail,nochef:" + nochef + "prenomchef=" + rs.getString("Prenomch") + "nomchef" + rs.getString("Nmch"));
        return chef;
    }
    //version 0
    //methode qui retoure l'horaire d'un professeur passe en param avec le type gradeaff paire ou impaire

    public static List<horairehebdo> AvoirHorairehebdopprof(int numprof, int annee, int gradeafftype) throws SQLException {
        Connection connection = null;
        int i = 0;
        List<horairehebdo> hpfL = new LinkedList<horairehebdo>();
        try {
            //      connection=getJdbcTables_isae().getConnection();
            connection = getJdbcIsae().getConnection();
        } catch (NamingException ex) {
            Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
        }
        //si on a le prem semestre cad gradeaff impaire
        if (gradeafftype == -1 | gradeafftype == 1 | gradeafftype == 3 | gradeafftype == 5 | gradeafftype == 7 | gradeafftype == 9 | gradeafftype == 11 | gradeafftype == 13) {

            PreparedStatement ps = connection.prepareStatement("select t2.noenseig,t3.IdSalle,t3.Jour,t3.PeriodeH,t5.Nochef,t5.intitule,t5.cnam,t1.numprof,t1.prenomprof,t1.nomprof,t6.spec,t6.gradeaff from Professeur as t1,Enseignement as t2,HoraireHebdo as t3,Section as t4,Matiere as t5,Affectation as t6 where t1.numprof=t2.numprof and t2.noenseig=t3.noenseig and t4.Nomatiere=t6.Nomatiere and t4.annee=t6.annee and t4.NoOuverture=t6.NoOuverture and t4.IdCentre=t6.IdCentre and t2.NoSection=t4.NoSection and t4.Nomatiere=t5.Nomatiere and t1.numprof=? and t6.annee=? group by t2.noenseig,t3.jour,t3.periodeH,t5.cnam,t1.numprof having (t6.gradeaff=-1 or t6.gradeaff=1 or t6.gradeaff=3 or t6.gradeaff=5 or t6.gradeaff=7 or t6.gradeaff=9 or t6.gradeaff=11 or t6.gradeaff=13);");
            ps.setInt(1, numprof);
            ps.setInt(2, annee);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                i++;
                hpfL.add(new horairehebdo(rs.getInt("noenseig"), rs.getInt("Jour"), rs.getInt("PeriodeH"), rs.getInt("Nochef")));
            }// fin du while
            connection.close();
            if(StaticClass.debug)System.out.println("Divertlistlist-avoirhoraieprof+++row fetched:" + i);
        }//end if impaire
        //si gradeafftype est paire
        else if (gradeafftype == 0 | gradeafftype == 2 | gradeafftype == 4 | gradeafftype == 6 | gradeafftype == 8 | gradeafftype == 10 | gradeafftype == 12 | gradeafftype == 14) {
            PreparedStatement ps = connection.prepareStatement("select t2.noenseig,t3.IdSalle,t3.Jour,t3.PeriodeH,t5.Nochef,t5.intitule,t5.cnam,t1.numprof,t1.prenomprof,t1.nomprof,t6.spec,t6.gradeaff from Professeur as t1,Enseignement as t2,HoraireHebdo as t3,Section as t4,Matiere as t5,Affectation as t6 where t1.numprof=t2.numprof and t2.noenseig=t3.noenseig and t4.Nomatiere=t6.Nomatiere and t4.annee=t6.annee and t4.NoOuverture=t6.NoOuverture and t4.IdCentre=t6.IdCentre and t2.NoSection=t4.NoSection and t4.Nomatiere=t5.Nomatiere and t1.numprof=? and t6.annee=? group by t2.noenseig,t3.jour,t3.periodeH,t5.cnam,t1.numprof having (t6.gradeaff=0 or t6.gradeaff=2 or t6.gradeaff=4 or t6.gradeaff=6 or t6.gradeaff=8 or t6.gradeaff=10 or t6.gradeaff=12 or t6.gradeaff=14);");
            ps.setInt(1, numprof);
            ps.setInt(2, annee);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                i++;
                hpfL.add(new horairehebdo(rs.getInt("noenseig"), rs.getInt("Jour"), rs.getInt("PeriodeH"), rs.getInt("Nochef")));
            }// fin du while
            connection.close();
            if(StaticClass.debug)System.out.println("Divertlistlist-avoirhoraieprof+++row fetched:" + i);

        }//end if paire
        return hpfL;

    }//fin de la methode avoirhoraireprof

    //version 1
    //numprof(bachir harb)=205,num(pascal)=5
    //cette methode prend le premier gradeaff du chaine des grades,et le test si il est paire ou impaire
    public static List<IntituleProf> AvoirMPparProfPJ(int annee, int numprof, int gradeaffL[], int p, int j) throws SQLException {
        Connection connection = null;

        List<IntituleProf> ipl = new LinkedList<IntituleProf>();
         if(StaticClass.debug)System.out.println("++++divertList++++avoirMPparprofPJ...1-debut,la liste vide est creer");
        if (gradeaffL != null) {

            if (numprof != 0) {
                //System.out.println("++++divertList++++avoirMPparprofPJ- 2-le num prof est diff de zero,numprof=" + numprof);
                /* String query = "select t1.intitule,t1.Nomatiere,t2.numprof,t2.prenomprof,t2.nomprof,t3.noenseig,t5.NoGroupe,t6.spec,t6.gradeaff ";
                query += " from Matiere as t1,Professeur as t2,Enseignement as t3,HoraireHebdo as t4,Section as t5,";
                query += " Affectation as t6 where t1.Nomatiere=t5.Nomatiere and t5.Nomatiere=t6.Nomatiere";
                query += " and t5.annee=t6.annee and t5.IdCentre=t6.IdCentre";
                query += " and t5.NoOuverture=t6.NoOuverture and t3.NoSection=t5.NoSection and t2.numprof=t3.numprof and t3.noenseig=t4.noenseig ";
                query += " and t6.annee=" + annee + " and t2.numprof=" + numprof + " and t4.PeriodeH=" + p + " and t4.Jour=" + j;
                //le groupe by est nous empeche de repeter les memes tuples d'une matiere qui appartient au pls spec
                query += " group by t1.intitule,t1.Nomatiere,t3.noenseig,t4.PeriodeH,t4.Jour having t6.gradeaff mod 2 = "+gradeaffL[0]+" mod 2;";
                 **/
                String query = "select distinct t1.intitule,t1.Nomatiere,t2.numprof,t2.prenomprof,t2.nomprof,t3.noenseig,t5.NoGroupe ";
                query += " from Matiere as t1,Professeur as t2,Enseignement as t3,HoraireHebdo as t4,Section as t5,";
                query += " Affectation as t6 where t1.Nomatiere=t5.Nomatiere and t5.Nomatiere=t6.Nomatiere";
                query += " and t5.annee=t6.annee and t5.IdCentre=t6.IdCentre";
                query += " and t5.NoOuverture=t6.NoOuverture and t3.NoSection=t5.NoSection and t2.numprof=t3.numprof and t3.noenseig=t4.noenseig ";
                query += " and t2.numprof=" + numprof + " and t4.PeriodeH=" + p + " and t4.Jour=" + j;
                query += " and t5.annee="+annee+" and abs(t6.gradeaff) mod 2 = abs("+ gradeaffL[0] +") mod 2; ";


                if (!NbPaire(gradeaffL[0])) {
                    if(StaticClass.debug)System.out.println("divertList+++avoirMPparProfPJ--gradeaff impaire-- la requete recuperer est:" + query);
                } else if (NbPaire(gradeaffL[0])) {
                   if(StaticClass.debug)System.out.println("divertList+++avoirMPparProfPJ--gradeaff paire-- la requete recuperer est:" + query);
                }


                //partie connection
                try {
                    //      connection=getJdbcTables_isae().getConnection();
                    connection = getJdbcIsae().getConnection();
                } catch (NamingException ex) {
                    Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
                }





                PreparedStatement ps = connection.prepareStatement(query);
                ResultSet rs = ps.executeQuery();
                if(StaticClass.debug)System.out.println("divertList+++avoirMPparProfPJ----Requete executee");

                while (rs.next()) {
                    //inttituleprof(intitule,numprof,prenomprof,nomprof,nomatiere)
                    //ipl.add(new IntituleProf(rs.getString("intitule"), rs.getInt("numprof"), rs.getString("prenomprof"), rs.getString("nomprof"), rs.getInt("Nomatiere"), rs.getInt("noenseig")));

                    IntituleProf ip = new IntituleProf(rs.getString("intitule"), rs.getInt("numprof"), rs.getString("prenomprof"), rs.getString("nomprof"), rs.getInt("Nomatiere"), rs.getInt("noenseig"));
                    ip.setNogroupe(rs.getInt("NoGroupe"));//variable optionnel
                    ipl.add(ip);
                    if(StaticClass.debug)System.out.println("++divertlist+++avoirMPparProfpj++++resultat de la requete:Periode:" + p + " jour:" + j + " " + rs.getString("intitule") + " numprof:" + rs.getInt("numprof") + " " + rs.getString("prenomprof") + " " + rs.getString("nomprof") + " Nomatiere" + rs.getInt("Nomatiere") + " noenseig:" + rs.getInt("noenseig"));
                }//fin du while

                connection.close();




            }//fin if numprof !=0
            else if (numprof == 0) {
                //System.out.println("++++divertList++++avoirMPparprofPJ+++le num prof=" + numprof);
            }
        }
        return ipl;
    }

    //----------------------------
    public static IntituleProf AvoirProf(int noenseig) throws SQLException {
        Connection connection = null;
        IntituleProf ip = null;

        try {
            //     connection=getJdbcTables_isae().getConnection();
            connection = getJdbcIsae().getConnection();
        } catch (NamingException ex) {
            Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
        }
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement("select t3.intitule,t4.numprof,t4.prenomprof,t4.nomprof,t3.Nomatiere,t1.noenseig from Enseignement as t1,Section as t2,Matiere as t3,Professeur as t4 where t1.NoSection=t2.NoSection and t2.Nomatiere=t3.Nomatiere and t4.numprof=t1.numprof and noenseig=?;");
        } catch (SQLException ex) {
            Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
        }
        ps.setInt(1, noenseig);
        ResultSet rs = ps.executeQuery();

        if(StaticClass.debug)System.out.println("++++@DivertList.");
        if (rs.first()) {
            ip = new IntituleProf(rs.getString("intitule"), rs.getInt("numprof"), rs.getString("prenomprof"), rs.getString("nomprof"), rs.getInt("Nomatiere"), rs.getInt("noenseig"));
            if(StaticClass.debug)System.out.println("+++++@DivertList.Avoirintituleprof---" + rs.getString("intitule") + "Nomatiere: " + rs.getInt("Nomatiere") + rs.getString("prenomprof"));
        } else {
            ip = new IntituleProf("0", 0, "0", "0", 0, 0);//initializer un ip pour eviter le null
        }
        connection.close();
        return ip;

    }
//-----------------------
    //v0
    //methode recupere un hash table du ueprof List
    //afin d'obtenir le groupe suivant prof1{ue1,ue2},prof2{ue3}

    public static Hashtable AvoirHashduUeProf(List<UeProf> upl) {

        Hashtable hashupl = new Hashtable<Integer, List<Integer>>();
        if (upl != null) {
            for (UeProf up : upl) {
                //si c'est la premiere insertion de ce prof il faut creer une list
                if (hashupl.get(up.getNumprof()) == null) {
                    List<Integer> l = new LinkedList<Integer>();
                    l.add(up.getNoenseig());
                    hashupl.put(up.getNumprof(), l);
                if(StaticClass.debug)System.out.println("+++++ divertlist++++AvoirHashduUeProf(upl) un nouveau key/val est creer numprof:" + up.getNumprof() + "la valeur noenseig " + up.getNoenseig());
                } else if (hashupl.containsKey(up.getNumprof())) {
                    List<Integer> l = (List<Integer>) hashupl.get(up.getNumprof());
                    l.add(up.getNoenseig());
                    hashupl.put(up.getNumprof(), l);

                    //pour le test
                    List<Integer> ltest = (List<Integer>) hashupl.get(up.getNumprof());

                    for (Integer i : ltest) {
                        if(StaticClass.debug)System.out.println("+++++ divertlist++++AvoirHashduUeProf(upl) le key/val obtenue-- numprof:" + up.getNumprof() + "la valeur noenseig " + i);
                    }
                }
            }//fin de for ueprof



        }//fin upl not null
        return hashupl;
    } //fin de la methode

    //methode utilise pour tester la presence d'un noenseg dans une liste UeProf ,mnehsom 3adam woujoudah bl ueprof
    public static boolean noenseigAppartient(List<UeProf> ueprofgfl, int noenseig) {
        for (UeProf up : ueprofgfl) {
            if (up.getNoenseig() == noenseig) {
                return true;
            }
        }
        return false;
    }

    //nombre paire ou impaire
    public static boolean NbPaire(int nb) {

        float nbf = (int) nb;
        //il faut utiliser le modulo %
        if (nbf / 2 == Math.round(nbf / 2)) {
            //System.out.println("+++divertlist++nbpaire:true");
            return true;

        } else {
            //System.out.println("+++divertlist++nbpaire:false");
            return false;
        }
    // si x%2==0 ==>pair else si x%2!=0 ==> impair

    }
// ce employe a le droit?,no=num employe,typdroit:"horaireisae"

    public static boolean AleDroit(int no, String typedroit) throws NamingException, SQLException {
        boolean res = false;
        Connection connection = null;
        String query = "select * from Users as t1,DroitAcces as t2 where t1.UserId=t2.UserId";
        query += " and t1.no=" + no + " and t2.typedroit=\"" + typedroit+"\"";

        if (StaticClass.debug) {
            if(StaticClass.debug)System.out.println("divertlist--Aledroit: " + query);
        }
        try {
            connection = getJdbcIsae().getConnection();

            PreparedStatement ps = null;
            ps = connection.prepareStatement(query);
            ResultSet rs = ps.executeQuery();
            if (rs.first()) {
                res = true;
            }

        } catch (SQLException ex) {
            Logger.getLogger(DivertList.class.getName()).log(Level.SEVERE, null, ex);
            if(StaticClass.debug)System.out.println(ex.getLocalizedMessage());
        }
        connection.close();
        return res;
    }

//local
    private static DataSource getJdbcIsae() throws NamingException {
        Context c = new InitialContext();
        return (DataSource) c.lookup("java:comp/env/jdbc/isae");
    }

    private static DataSource getJdbcTables_isae() throws NamingException {
        Context c = new InitialContext();
        return (DataSource) c.lookup("java:comp/env/jdbc/tables_isae");
    }

    private DataSource getJdbcDb_isae() throws NamingException {
        Context c = new InitialContext();
        return (DataSource) c.lookup("java:comp/env/jdbc/db_isae");
    }
} // fin de la classe
